package com.emar.spring.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.emar.spring.common.util.StringUtil;
import com.emar.spring.dto.ParamsDTO;
import com.emar.spring.model.ProjectEntity;
/**
 * 操作项目表的类
 */
@Repository
public class ProjectDao extends BaseDAO{

	private String getWhereSql(ProjectEntity pe){
		StringBuffer sb = new StringBuffer();
		if(StringUtil.isNotZero( pe.getProjectId())){
			sb.append( " and p.`ProjectID` = "+pe.getProjectId());
		}
		if(StringUtil.isNotEmpty(pe.getProjectName())){
			sb.append( " and p.`ProjectName` =  '"+pe.getProjectName()+"'");
		}
		if(StringUtil.isNotEmpty(pe.getProjectType())){
			sb.append( " and p.`ProjectType` = '"+pe.getProjectType()+"'");
		}
		if(StringUtil.isNotEmpty(pe.getProjectStatus())){
			sb.append( " and p.`ProjectStatus` = '"+pe.getProjectStatus()+"'");
		}
		if(StringUtil.isNotEmpty(pe.getCommissionCorpName())){
			sb.append( " and p.`CommissionCorpName` = '"+pe.getCommissionCorpName()+"'");
		}
		if(StringUtil.isNotEmpty( pe.getCommissionCorpLinkName())){
			sb.append( " and p.`CommissionCorpLinkName` = '"+pe.getCommissionCorpLinkName()+"'");
		}
		return sb.toString().replaceFirst( "and",  "where");
	}
	
	private String getListSql(ProjectEntity pe ,String groupBy, int pageNumber,int pageSize){
		StringBuffer sb = new StringBuffer();
		sb.append( " SELECT ");
		sb.append( " ,p.`ProjectID` projectID ");
		sb.append( " ,p.`ProjectName` projectName ");
		sb.append( " ,p.`ProjectCode` projectCode ");
		sb.append( " ,p.`ProjectType` projectType ");
		sb.append( " ,p.`ProjectStatus` projectStatus ");
		sb.append( " ,p.`ProjectDescribe` projectDescribe ");
		sb.append( " ,p.`ProjectTaskDescribe` projectTaskDescribe ");
		sb.append( " ,p.`ProjectManagerID` projectManagerID ");
		sb.append( " ,p.`CommissionCorpName` commissionCorpName ");
		sb.append( " ,p.`CommissionDate` commissionDate ");
		sb.append( " ,p.`CommissionCorpLinkName` commissionCorpLinkName ");
		sb.append( " ,p.`CommissionCorpLinkTel` commissionCorpLinkTel ");
		sb.append( " ,p.`TargetSystemName` TargetSystemName ");
		if(StringUtil.isNotEmpty(groupBy)){
			sb.append( " ,COUNT(*) AS COUNT ");
		}
		sb.append( " FROM t_project AS p  ");
		sb.append(this.getWhereSql(pe));
		if(StringUtil.isNotEmpty(groupBy)){
			sb.append( " GROUP BY ("+groupBy+")");
		}
		return sb.toString().replaceFirst( ",",  "");
	}
	
	/**
	 * 拼接查询结果总数的sql
	 * @param user 
	 * @return
	 */
	private String getCountSql(ProjectEntity pe){
		StringBuffer sb = new StringBuffer();
		sb.append( " SELECT ");
		sb.append( " count(*) ");
		sb.append( " FROM t_project AS p ");
		sb.append(this.getWhereSql(pe));
		return sb.toString();
	}
	
	/**
	 * 以单位名称分组，获取总数
	 * @return	返回获取的总数
	 */
	public int getCorpCount(String groupBy){
		String sql= "SELECT COUNT(*) FROM (SELECT COUNT("+groupBy+") FROM t_project AS p GROUP BY ("+groupBy+") ) AS b ";
		return super.getCount(sql);
	}
	/**
	 * 获取项目总数
	 * @return
	 */
	public int getProjectCount(ProjectEntity pe){
		String sql = this.getCountSql(pe);
		return super.getCount(sql);
	}
	
	/**
	 * 不分组时，获取正常的实体对象
	 * @return
	 */
	public List<ProjectEntity> getProjectPageList(ProjectEntity pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe,groupBy, pageNumber, pageSize);
		List<ProjectEntity> list = this.getPageList(sql, ProjectEntity.class,pageNumber,pageSize);
		return list;
	}
	
	public List<ProjectEntity> getProjectList(ProjectEntity pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe, groupBy,pageNumber, pageSize);
		List<ProjectEntity> list = this.getList(sql, ProjectEntity.class);
		return list;
	}
	
	/**
	 * 有分组时，获取dto对象
	 * @param pe
	 * @param groupBy
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<ParamsDTO> getProjectDTOPageList(ProjectEntity pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe,groupBy, pageNumber, pageSize);
		List<ParamsDTO> list = this.getPageList(sql, ParamsDTO.class,pageNumber,pageSize);
		return list;
	}
	
	public List<ParamsDTO> getProjectDTOList(ProjectEntity pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe,groupBy, pageNumber, pageSize);
		List<ParamsDTO> list = this.getList(sql,ParamsDTO.class);
		return list;
	}
}
